﻿using Microsoft.SharePoint;
using Microsoft.SharePoint.Administration;
using Navicon.SP.FieldTypes.LookupTable;
using Navicon.SP.FieldTypes.LookupTree;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace PopulateListsFromExcel
{
    public partial class MainForm : Form
    {
        public MainForm()
        {
            InitializeComponent();
        }

        SPWebApplication currentWebApp = null;
        SPWebApplicationCollection webApplications = null;
        SPSite currentSite = null;
        SPWeb currentWeb = null;

        private static Dictionary<string,Dictionary<int, SPListItem>> ListCaches = new Dictionary<string,Dictionary<int,SPListItem>>();

        private void MainForm_Load(object sender, EventArgs e)
        {
            SPServiceCollection services = SPFarm.Local.Services;
            
            foreach (SPService curService in services)
            {
               cbApps.Items.Clear();
               webApplications = SPWebService.ContentService.WebApplications;
               int i = 0;
               foreach (SPWebApplication webApp in webApplications)
               {
                   cbApps.Items.Add(webApp.Name);
                   if (webApp.Sites[0].Url == Program.Url)
                   {
                       cbApps.SelectedIndex = i;
                       return;
                   }
                   i++;
               }
            }

            if (cbApps.SelectedIndex <0)
            {
                cbApps.SelectedIndex = 0;
            }
        }

        private void cbApps_SelectedIndexChanged(object sender, EventArgs e)
        {
            currentWebApp = webApplications[cbApps.Text];
            cbSites.Items.Clear();
            int i = 0;
            foreach (SPSite site in currentWebApp.Sites)
            {
                cbSites.Items.Add(site.Url);
                if (Program.Url == site.Url)
	            {
                    cbSites.SelectedIndex = i;
                    break;
	            }
                i++;
            }
            if (cbSites.SelectedIndex < 0 )
            {
                cbSites.SelectedIndex = 0;  
            }
        }

        private void cbSites_SelectedIndexChanged(object sender, EventArgs e)
        {
            currentSite = currentWebApp.Sites[cbSites.Text];
            cbWebs.Items.Clear();
           
            foreach (SPWeb web in currentSite.AllWebs)
            {
                cbWebs.Items.Add(web.ServerRelativeUrl);
            }
            cbWebs.SelectedIndex = 0;
        }

        private void cbWebs_SelectedIndexChanged(object sender, EventArgs e)
        {
            currentWeb = currentSite.OpenWeb(cbWebs.Text);
            if (Program.ExcelFileName != null)
            {
                ListCaches = new Dictionary<string,Dictionary<int,SPListItem>>();
            
                string fileName = Program.ExcelFileName;
                try
                {
                
                DataSet dataset = AdoExcel.ReadExcelFile(fileName);
                for (decimal i = 1; i <= txtNumberOfRuns.Value; i++)
                {
                    ListCaches = new Dictionary<string, Dictionary<int, SPListItem>>();
                    RunImportIteration(dataset);
                }

                
                }
                catch (InvalidOperationException invalidException)
                {
                    if (MessageBox.Show(
                    "Please, install Sharepoint Designer and Microsoft Access Database Engine 2010 Redistributable ", "Download?", MessageBoxButtons.YesNo, MessageBoxIcon.Asterisk
                ) == DialogResult.Yes)
                    {
                        System.Diagnostics.Process.Start("https://www.microsoft.com/en-us/download/confirmation.aspx?id=13255");
                    }

                }
                if (Program.isConsole)
                {
                    this.Close();
                }
                else
                {
                    MessageBox.Show("Готово!");
                }
            }
        }

       
        private void button2_Click(object sender, EventArgs e)
        {
            ListCaches = new Dictionary<string,Dictionary<int,SPListItem>>();

            if ( openFileDialog1.ShowDialog() == DialogResult.OK)
            {
                string fileName = openFileDialog1.FileName;
                try
                {
                    DataSet dataset = AdoExcel.ReadExcelFile(fileName);
                    for (decimal i = 1; i <= txtNumberOfRuns.Value; i++)
                    {
                        ListCaches = new Dictionary<string, Dictionary<int, SPListItem>>();
                        RunImportIteration(dataset);
                    }
                }
                catch (InvalidOperationException invalidException)
                {
                    if (MessageBox.Show(
                    "Please, install Sharepoint Designer and Microsoft Access Database Engine 2010 Redistributable ", "Download?", MessageBoxButtons.YesNo, MessageBoxIcon.Asterisk
                ) == DialogResult.Yes)
                    {
                        System.Diagnostics.Process.Start("https://www.microsoft.com/en-us/download/confirmation.aspx?id=13255");
                    }

                }
               
                if (Program.isConsole)
                {
                    this.Close();
                }
                else
                {
                    MessageBox.Show("Готово!");
                }
            }
        }

        private void RunImportIteration(DataSet dataset)
        {
            foreach (DataTable table in dataset.Tables)
            {
               
                try
                {
                    SPList list = null;
                    if (table.TableName.Contains("SPBookmark") || table.TableName.Contains("_FilterDatabase"))
                    {
                        continue;
                    }
                    if (table.TableName.StartsWith("#"))
                    {
                        continue;
                    }
                    if (table.TableName.StartsWith("-"))
                    {
                        continue;
                    }
                    if (table.TableName.StartsWith(@"\"))
                    {
                        continue;
                    }
                    if (table.TableName.Contains(@"\"))
                    {
                        list = currentWeb.GetList(table.TableName.Replace("'_xlnm#_FilterDatabase", ""));
                    }
                    else
                    {
                        list = currentWeb.Lists[table.TableName.Replace("'_xlnm#_FilterDatabase", "")];
                    }


                    if (ListCaches.ContainsKey(list.Title) == false)
                    {
                        var listCache = SPListItemsIndexator.CreateIndices(currentWeb, list, "Title");
                        ListCaches.Add(list.Title, listCache);
                    }

                    string uniqueFieldName = "Title";
                    bool useUniqueField = false;
                    if (list.Fields.ContainsField("UniqueField"))
                    {
                        uniqueFieldName = "UniqueField";
                        useUniqueField = true;
                    }

                    string previousTitle = string.Empty;
                    int itemIndex = 0;
                    foreach (DataRow row in table.Rows)
                    {
                        //SPListItem newItem =  SPListItemsIndexator.GetItemFromCache(ListCaches[list.Title], "Title", row[0].ToString());
                        string currentTitle = row[0].ToString();
                        if (useUniqueField)
                        {
                            currentTitle = row["UniqueField"].ToString();
                        }
                        if (string.Equals(currentTitle, previousTitle))
                        {
                            itemIndex++;
                        }
                        else
                        {
                            itemIndex = 0;
                        }
                        SPQuery query = new SPQuery();
                        query.Query = String.Format(@"<Where>
			                            <Eq>
				                            <FieldRef Name='"+uniqueFieldName+@"' />
				                            <Value Type='Text'>{0}</Value>
			                            </Eq>
                            </Where>", currentTitle);

                        SPListItemCollection items = list.GetItems(query);
                        SPListItem newItem = null;
                        if (items.Count > itemIndex)
                        {
                            newItem = items[itemIndex];
                        }

                        if (newItem == null)
                        {
                            newItem = list.AddItem();
                        }

                        foreach (DataColumn column in table.Columns)
                        {
                            try
                            {
                                column.ColumnName = column.ColumnName.Trim();
                                newItem[column.ColumnName] = GetFieldValue(list, column, row[column]);
                            }
                            catch (Exception)
                            {

                            }
                        }

                        previousTitle = currentTitle;
                        newItem.Update();
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Проблема со списком: " + table.TableName + Environment.NewLine + " " + ex + Environment.NewLine);

                }
            }
        }

        private object GetFieldValue(SPList list, DataColumn column, object columnValue)
        {
            columnValue = columnValue.ToString().Trim();
            SPField field = null;
            try
            {
                field = list.Fields[column.ColumnName];
            }
            catch (Exception)
            {
                field = list.Fields.GetFieldByInternalName(column.ColumnName);
            }

            switch (field.Type)
            {
                case SPFieldType.Integer:
                case SPFieldType.Number:
                case SPFieldType.Text:
                case SPFieldType.Note:
                case SPFieldType.Choice:
                    return columnValue.ToString();

                case SPFieldType.User:
                    SPList lookupUserList = list.ParentWeb.Lists[new Guid(((SPFieldUser)field).LookupList)];
                    if (ListCaches.ContainsKey(lookupUserList.Title) == false)
                    {
                        //var listCache = SPListItemsIndexator.CreateIndices(currentWeb, lookupUserList, "Учетная запись");
                        var listCache = SPListItemsIndexator.CreateIndices(currentWeb, lookupUserList, "Title");
                        //var listCache = SPListItemsIndexator.CreateIndices(currentWeb, lookupUserList, "ImnName");
                        ListCaches.Add(lookupUserList.Title, listCache);
                    }

                    //string userValue2 = SPListItemsIndexator.GetLookupValueFromCache(ListCaches[lookupUserList.Title], "Учетная запись", columnValue.ToString()).ToString();
                    object user = SPListItemsIndexator.GetLookupValueFromCache(ListCaches[lookupUserList.Title], "Title", columnValue.ToString());
                    //object user = SPListItemsIndexator.GetLookupValueFromCache(ListCaches[lookupUserList.Title], "ImnName", columnValue.ToString());
                    if (user == null)
                    {
                        SPUser spUser = currentWeb.EnsureUser(columnValue.ToString());
                        if (spUser != null)
                        {
                            return spUser;
                        }
                        else
                        {
                            throw new Exception(string.Format("user {0} does not exist", columnValue));
                        }
                    }
                    string userValue2 = user.ToString();
                    return userValue2.Replace("i:0#.w|", string.Empty);

                case SPFieldType.Lookup:
                case SPFieldType.Invalid:

                    if ((string.Equals(field.TypeAsString, "LookupTable", StringComparison.InvariantCultureIgnoreCase)))
                    {
                        SPList lookupList = list.ParentWeb.Lists[new Guid(((LookupTable)field).LookupList)];
                        if (ListCaches.ContainsKey(lookupList.Title) == false)
                        {
                            var listCache = SPListItemsIndexator.CreateIndices(currentWeb, lookupList, "Title");
                            ListCaches.Add(lookupList.Title, listCache);
                        }

                        return SPListItemsIndexator.GetLookupValueFromCache(ListCaches[lookupList.Title], "Title", columnValue.ToString());
                    }
                    else if (string.Equals(field.TypeAsString, "LookupTree", StringComparison.InvariantCultureIgnoreCase))
                    {
                        SPList lookupList = list.ParentWeb.Lists[new Guid(((LookupTree)field).LookupList)];
                        if (ListCaches.ContainsKey(lookupList.Title) == false)
                        {
                            var listCache = SPListItemsIndexator.CreateIndices(currentWeb, lookupList, "Title");
                            ListCaches.Add(lookupList.Title, listCache);
                        }

                        return SPListItemsIndexator.GetLookupValueFromCache(ListCaches[lookupList.Title], "Title", columnValue.ToString());
                    }

                    break;
                case SPFieldType.Boolean:
                    return bool.Parse(columnValue.ToString());
                default:
                    break;
            }

            return string.Empty;
        }

        private void checkBox1_CheckedChanged(object sender, EventArgs e)
        {

        }
    }
}
